'''
将模型生成批量导入taskwear得excel文件，批量创建表语句
'''
import pandas as pd

# 读取Excel文件
excel_file = r'E:\git_dtsw_jx\devcustomerservices_jiangxi\share\doc\江西_数据共享_大唐数据订阅模版-共享模型v1-差1张pm小区表.xlsx'
output_file = r'E:\tc_bak\work\江西项目\功能应用\小站模型开发\hive_table_create_202509.xlsx'
df = pd.read_excel(excel_file,sheet_name='导航',header=0)
# 假设要筛选‘内部编号’为多个指定值的多行，例如选出‘内部编号’为1, 3, 5的多行
selected_ids = ['dm.1','dm.2','dm.10']

df = df[df['内部编号'].isin(selected_ids)]
#
# # 打印DataFrame内容
# # 假设表英文名对应的列名为表英文名或table_name，请根据实际列名修改
# # print(df['表英文名'][0])
# # 只读取多个sheet中‘PDM字段英文名’和‘PDM字段类型’两列，sheet名称为指定列表
dfs = []
filtered_rows = []
# 需要过滤的字段名
filter_fields = ['p_date', 'p_hour', 'p_quarter', 'p_provincecode', 'p_year', 'p_month', 'p_week']
print(selected_ids)
for sheet in selected_ids:
    try:
        df_sheet = pd.read_excel(excel_file, sheet_name=sheet, header=None)
        # 查找PDM字段英文名、PDM字段类型、PDM字段中文名（说明）所在的单元格位置
        pdm_row, pdm_col = None, None
        pdm_type_col = None  # 字段类型列号
        pdm_desc_col = None  # 字段类型行号
        for i in range(df_sheet.shape[0]):
            for j in range(df_sheet.shape[1]):
                cell_val = str(df_sheet.iat[i, j]).strip()
                if cell_val == 'PDM字段英文名':
                    pdm_row, pdm_col = i, j
                if cell_val == 'PDM字段类型':
                    pdm_type_col = j
                if cell_val == 'PDM字段中文名（说明）':
                    pdm_desc_col = j
            if pdm_row is not None and pdm_type_col is not None and pdm_desc_col is not None:
                break
        # 查找目标表英文名所在的单元格，并获取其下一行同一列的值
        target_table_name = None
        for i in range(df_sheet.shape[0] - 1):
            for j in range(df_sheet.shape[1]):
                if str(df_sheet.iat[i, j]).strip() == '目标表英文名':
                    target_table_name = str(df_sheet.iat[i + 1, j]).strip()
                    break
            if target_table_name is not None:
                break
        if pdm_row is not None and pdm_type_col is not None and pdm_desc_col is not None:
            # 取PDM字段英文名这一行作为表头，往下所有该列有值的行
            header = df_sheet.iloc[pdm_row]
            data = df_sheet.iloc[pdm_row+1:]
            # 只保留PDM字段英文名列下方有值的行
            data = data[data[pdm_col].notnull() & (data[pdm_col].astype(str).str.strip() != '')]
            data.columns = header
            # 只保留‘PDM字段英文名’、‘PDM字段类型’、‘PDM字段中文名（说明）’三列
            data = data[['PDM字段英文名', 'PDM字段类型', 'PDM字段中文名（说明）']]
            data = data.reset_index(drop=True)
            # sheet_name列改为目标表英文名
            data['sheet_name'] = target_table_name if target_table_name is not None else sheet
            # 字段描述列重命名
            data = data.rename(columns={'PDM字段中文名（说明）': '字段描述'})
            data = data.rename(columns={'PDM字段英文名': '字段'})
            data = data.rename(columns={'PDM字段类型': '字段类型'})
            # 过滤需要去除的字段
            mask = data['字段'].isin(filter_fields)
            filtered = data[mask]
            remain = data[~mask]
            dfs.append(remain)

            # 被过滤的行加入filtered_rows
            if not filtered.empty:
                filtered_row = {
                    'sheet_name': target_table_name if target_table_name is not None else sheet,
                    '字段': ','.join(filtered['字段'].astype(str)),
                    '字段类型': ','.join(filtered['字段类型'].astype(str)),
                    '字段描述': ','.join(filtered['字段描述'].astype(str))
                }
                filtered_rows.append(filtered_row)
        else:
            print(f"未找到sheet {sheet} 中的'PDM字段英文名'、'PDM字段类型'或'PDM字段中文名（说明）'，请检查格式。")
    except Exception as e:
        print(f"读取sheet {sheet} 时出错: {e}")
# 合并被过滤掉的行为一个DataFrame
if filtered_rows:
    df_filtered = pd.DataFrame(filtered_rows)
    df_merged = pd.merge(df, df_filtered, left_on='表英文名', right_on='sheet_name', how='left')
    print(df_merged)
# 定义新表的列名
columns = [
    "编号", "表名", "中文名称", "责任人", "层级", "存储格式", "分隔符", "压缩格式", "表类型", "描述",
    "数据标签", "生命周期(天）", "开发环境路径前缀", "存储路径", "分区字段", "分区字段类型", "分区字段描述",
    "天分区字段", "天分区格式", "表属性", "生命周期类型"
]

# 创建DataFrame
df_new = pd.DataFrame(columns=columns)
df_new['编号'] = range(1,len(df_merged)+1)
df_new['表名'] = df_merged['表英文名']
df_new['中文名称'] = df_merged['表中文名']
df_new['责任人'] = 'tianchuan'
df_new['层级'] = 'FACT'
df_new['存储格式'] = 'parquet'
df_new['分隔符'] = ''
df_new['压缩格式'] = 'false'
df_new['表类型'] = '外部表'
df_new['描述'] = ''
df_new['数据标签'] = df_merged['二级分类']
df_new['生命周期(天）'] = df_merged['生命周期（存储周期单位：天）'].astype(str).str.extract('(\d+)')[0]
df_new['开发环境路径前缀'] = '/tenants/dtsw_jx/wireless/dev_dtsw_pro'
df_new['存储路径'] = '/fact/' + df_merged['表英文名']
df_new['分区字段'] = df_merged['字段']
df_new['分区字段类型'] = df_merged['字段类型']
df_new['分区字段描述'] = df_merged['字段描述']
df_new['天分区字段'] = 'p_date'
df_new['天分区格式'] = 'yyyy-MM-dd'
df_new['表属性'] = ''
df_new['生命周期类型'] = df_merged['字段'].apply(
    lambda x: 'PERMANENT' if any(p in str(x) for p in ['p_year', 'p_week', 'p_month']) else 'LIMITED'
)
# 打印新建的DataFrame
# print(df_new)
# 将df_new作为第一个sheet，dfs中的每个DataFrame依次写入后续sheet
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    # 写入第一个sheet，第一行置空，从第二行开始写入数据
    # 先写入空行
    empty_row = pd.DataFrame([[""] * len(df_new.columns)], columns=df_new.columns)
    empty_row.iloc[0, 0] = """填写须知：
（1）红色字段为必填字段，黑色字段为选填字段
（2）文件大小不超过2M
（3）不允许输入重复的“编号”、“表名”
（4）必须存在与“编号”相对应的同名的sheet页
（5）“数据标签”、“分区字段”、“分区字段类型”、“分区字段描述”填写时的逗号均为英文逗号
（6）当“存储格式”为“TEXTFILE”时，“分隔符”为必填字段
（7）当“分区字段”不为空时，“分区字段类型”、“分区字段描述”、为必填字段，格式(字段名，字段类型， 字段描述）
（8）当“生命周期类型”为LIMITED（有限）时,“天分区字段”、“天分区格式”为必填字段（只能设置一个）
（9）不允许删除本行提示"""
    empty_row.to_excel(writer, sheet_name='all', index=False, header=False)
    # 再写入数据，从第二行开始
    df_new.to_excel(writer, sheet_name='all', index=False, header=True, startrow=1)
    # 假设dfs是一个DataFrame列表
    for i, df_tmp in enumerate(dfs):
        # 移除'sheet_name'列（如果存在）
        if 'sheet_name' in df_tmp.columns:
            df_tmp = df_tmp.drop(columns=['sheet_name'])
        # 在最前面插入'编号'列，从0开始自增
        df_tmp.insert(0, '编号', range(0, len(df_tmp)))
        sheet_name = f'{i+1}'
        df_tmp.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"新建的DataFrame及所有dfs已成功输出到: {output_file}")